﻿Imports System.Data.SqlClient
Imports System.Data.Odbc

Public Class clsMGW10009
    Private mintIdAutobus As Integer
    Private mstrNoEconomico As String
    Private mintIdRuta As Integer
    Private mintTipoKms As Integer
    Private mstrCod_Vehiculo As String
    Private mstrNumero_Esclavo As String
    Private mintcIdClien01 As Integer
    Private mstrcCodigoC01 As String
    Private mstrcRazonSo01 As String
    Private mstrPlacas As String
    Private mstrMarca As String
    Private mstrModelo As String
    Private mstrSerie As String
    Private mstrMotor As String
    Private mintEstatus As Integer
    Private mdblImporte01 As Double
    Private mdblImporte02 As Double
    Private mdblImporte03 As Double
    Private mdblImporte04 As Double
    Private mdblImporte05 As Double
    Private mstrTexto01 As String
    Private mstrTexto02 As String
    Private mstrTexto03 As String
    Private mstrTexto04 As String
    Private mstrTexto05 As String
    Public Property IdAutobus As Integer
        Get
            Return mintIdAutobus
        End Get
        Set(ByVal value As Integer)
            mintIdAutobus = value
        End Set
    End Property
    Public Property NoEconomico As String
        Get
            Return mstrNoEconomico
        End Get
        Set(ByVal value As String)
            mstrNoEconomico = value
        End Set
    End Property
    Public Property IdRuta As Integer
        Get
            Return mintIdRuta
        End Get
        Set(ByVal value As Integer)
            mintIdRuta = value
        End Set
    End Property
    Public Property TipoKms As Integer
        Get
            Return mintTipoKms
        End Get
        Set(ByVal value As Integer)
            mintTipoKms = value
        End Set
    End Property
    Public Property Cod_Vehiculo As String
        Get
            Return mstrCod_Vehiculo
        End Get
        Set(ByVal value As String)
            mstrCod_Vehiculo = value
        End Set
    End Property
    Public Property Numero_Esclavo As String
        Get
            Return mstrNumero_Esclavo
        End Get
        Set(ByVal value As String)
            mstrNumero_Esclavo = value
        End Set
    End Property
    Public Property cIdClien01 As Integer
        Get
            Return mintcIdClien01
        End Get
        Set(ByVal value As Integer)
            mintcIdClien01 = value
        End Set
    End Property
    Public Property cCodigoC01 As String
        Get
            Return mstrcCodigoC01
        End Get
        Set(ByVal value As String)
            mstrcCodigoC01 = value
        End Set
    End Property
    Public Property cRazonSo01 As String
        Get
            Return mstrcRazonSo01
        End Get
        Set(ByVal value As String)
            mstrcRazonSo01 = value
        End Set
    End Property
    Public Property Placas As String
        Get
            Return mstrPlacas
        End Get
        Set(ByVal value As String)
            mstrPlacas = value
        End Set
    End Property
    Public Property Marca As String
        Get
            Return mstrMarca
        End Get
        Set(ByVal value As String)
            mstrMarca = value
        End Set
    End Property
    Public Property Modelo As String
        Get
            Return mstrModelo
        End Get
        Set(ByVal value As String)
            mstrModelo = value
        End Set
    End Property
    Public Property Serie As String
        Get
            Return mstrSerie
        End Get
        Set(ByVal value As String)
            mstrSerie = value
        End Set
    End Property
    Public Property Motor As String
        Get
            Return mstrMotor
        End Get
        Set(ByVal value As String)
            mstrMotor = value
        End Set
    End Property
    Public Property Estatus As Integer
        Get
            Return mintEstatus
        End Get
        Set(ByVal value As Integer)
            mintEstatus = value
        End Set
    End Property
    Public Property Importe01 As Double
        Get
            Return mdblImporte01
        End Get
        Set(ByVal value As Double)
            mdblImporte01 = value
        End Set
    End Property
    Public Property Importe02 As Double
        Get
            Return mdblImporte02
        End Get
        Set(ByVal value As Double)
            mdblImporte02 = value
        End Set
    End Property
    Public Property Importe03 As Double
        Get
            Return mdblImporte03
        End Get
        Set(ByVal value As Double)
            mdblImporte03 = value
        End Set
    End Property
    Public Property Importe04 As Double
        Get
            Return mdblImporte04
        End Get
        Set(ByVal value As Double)
            mdblImporte04 = value
        End Set
    End Property
    Public Property Importe05 As Double
        Get
            Return mdblImporte05
        End Get
        Set(ByVal value As Double)
            mdblImporte05 = value
        End Set
    End Property
    Public Property Texto01 As String
        Get
            Return mstrTexto01
        End Get
        Set(ByVal value As String)
            mstrTexto01 = value
        End Set
    End Property
    Public Property Texto02 As String
        Get
            Return mstrTexto02
        End Get
        Set(ByVal value As String)
            mstrTexto02 = value
        End Set
    End Property
    Public Property Texto03 As String
        Get
            Return mstrTexto03
        End Get
        Set(ByVal value As String)
            mstrTexto03 = value
        End Set
    End Property
    Public Property Texto04 As String
        Get
            Return mstrTexto04
        End Get
        Set(ByVal value As String)
            mstrTexto04 = value
        End Set
    End Property
    Public Property Texto05 As String
        Get
            Return mstrTexto05
        End Get
        Set(ByVal value As String)
            mstrTexto05 = value
        End Set
    End Property
    Public Sub New()
        mintIdAutobus = 0
        mstrNoEconomico = ""
        mintIdRuta = 0
        mintTipoKms = 0
        mstrCod_Vehiculo = ""
        mstrNumero_Esclavo = 0
        mintcIdClien01 = 0
        mstrcCodigoC01 = ""
        mstrcRazonSo01 = ""
        mstrPlacas = ""
        mstrMarca = ""
        mstrModelo = ""
        mstrSerie = ""
        mstrMotor = ""
        mintEstatus = 0
        mdblImporte01 = 0
        mdblImporte02 = 0
        mdblImporte03 = 0
        mdblImporte04 = 0
        mdblImporte05 = 0
        mstrTexto01 = ""
        mstrTexto02 = ""
        mstrTexto03 = ""
        mstrTexto04 = ""
        mstrTexto05 = ""
    End Sub
    Public Sub Guardar()
        If IdAutobus = 0 Then
            Exit Sub
        End If

        Dim intRetVal As Integer = BuscarIntDatoCitra("Autobuses", "IdAutobus", "IdAutobus=" & IdAutobus)

        If intRetVal = 0 Then
            Insertar()
        Else
            Actualizar()
        End If
    End Sub

    Public Sub Buscar()
        Dim cnConn As New SqlConnection
        Dim cmdBuscar As New SqlCommand
        Dim rdBuscar As SqlDataReader

        Try
            cnConn.ConnectionString = CitraConnection
            cmdBuscar.Connection = cnConn
            cmdBuscar.CommandText = "SELECT * FROM Autobuses WHERE cCodigoC01='" & cCodigoC01 & "'"

            cnConn.Open()
            rdBuscar = cmdBuscar.ExecuteReader

            If rdBuscar.Read() Then
                mstrNoEconomico = rdBuscar("NoEconomico").ToString.Trim
                mintIdRuta = rdBuscar("IdRuta")
                mintTipoKms = rdBuscar("TipoKms")
                mstrCod_Vehiculo = rdBuscar("Cod_Vehiculo").ToString.Trim
                mstrNumero_Esclavo = rdBuscar("Numero_Esclavo").ToString.Trim
                mintcIdClien01 = rdBuscar("cIdClien01")
                mstrcCodigoC01 = rdBuscar("cCodigoC01").ToString.Trim
                mstrcRazonSo01 = rdBuscar("cRazonSo01").ToString.Trim
                mstrPlacas = rdBuscar("Placas").ToString.Trim
                mstrMarca = rdBuscar("Marca").ToString.Trim
                mstrModelo = rdBuscar("Modelo").ToString.Trim
                mstrSerie = rdBuscar("Serie").ToString.Trim
                mstrMotor = rdBuscar("Motor").ToString.Trim
                mintEstatus = rdBuscar("Estatus")
                mdblImporte01 = rdBuscar("Importe01")
                mdblImporte02 = rdBuscar("Importe02")
                mdblImporte03 = rdBuscar("Importe03")
                mdblImporte04 = rdBuscar("Importe04")
                mdblImporte05 = rdBuscar("Importe05")
                mstrTexto01 = rdBuscar("Texto01").ToString.Trim
                mstrTexto02 = rdBuscar("Texto02").ToString.Trim
                mstrTexto03 = rdBuscar("Texto03").ToString.Trim
                mstrTexto04 = rdBuscar("Texto04").ToString.Trim
                mstrTexto05 = rdBuscar("Texto05").ToString.Trim
            Else
                mstrNoEconomico = ""
                mintIdRuta = 0
                mintTipoKms = 0
                mstrCod_Vehiculo = ""
                mstrNumero_Esclavo = ""
                mintcIdClien01 = 0
                mstrcCodigoC01 = ""
                mstrcRazonSo01 = ""
                mstrPlacas = ""
                mstrMarca = ""
                mstrModelo = ""
                mstrSerie = ""
                mstrMotor = ""
                mintEstatus = 0
                mdblImporte01 = 0
                mdblImporte02 = 0
                mdblImporte03 = 0
                mdblImporte04 = 0
                mdblImporte05 = 0
                mstrTexto01 = ""
                mstrTexto02 = ""
                mstrTexto03 = ""
                mstrTexto04 = ""
                mstrTexto05 = ""
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)
        Finally
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End Try

    End Sub
    Public Sub Insertar()
        Dim sql As String = "Insert Into Autobuses (" & _
                            "IdAutobus," & _
                            "NoEconomico," & _
                            "IdRuta," & _
                            "TipoKms," & _
                            "Cod_Vehiculo," & _
                            "Numero_Esclavo," & _
                            "cIdClien01," & _
                            "cCodigoC01," & _
                            "cRazonSo01," & _
                            "Placas," & _
                            "Marca," & _
                            "Modelo," & _
                            "Serie," & _
                            "Motor," & _
                            "Estatus," & _
                            "Importe01," & _
                            "Importe02," & _
                            "Importe03," & _
                            "Importe04," & _
                            "Importe05," & _
                            "Texto01," & _
                            "Texto02," & _
                            "Texto03," & _
                            "Texto04," & _
                            "Texto05) " & _
                            "VALUES (" & _
                            "@IdAutobus," & _
                            "substring(@NoEconomico,1,40)," & _
                            "@IdRuta," & _
                            "@TipoKms," & _
                            "substring(@Cod_Vehiculo,1,20)," & _
                            "@Numero_Esclavo," & _
                            "@cIdClien01," & _
                            "substring(@cCodigoC01,1,40)," & _
                            "substring(@cRazonSo01,1,20)," & _
                            "@Placas," & _
                            "@Marca," & _
                            "@Modelo," & _
                            "@Serie," & _
                            "@Motor," & _
                            "@Estatus," & _
                            "@Importe01," & _
                            "@Importe02," & _
                            "@Importe03," & _
                            "@Importe04," & _
                            "@Importe05," & _
                            "@Texto01," & _
                            "@Texto02," & _
                            "@Texto03," & _
                            "@Texto04," & _
                            "@Texto05) "


        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        Dim Command As New SqlCommand(sql, cnConn)

        Command.Parameters.AddWithValue("IdAutobus", mintIdAutobus)
        Command.Parameters.AddWithValue("NoEconomico", mstrNoEconomico)
        Command.Parameters.AddWithValue("IdRuta", mintIdRuta)
        Command.Parameters.AddWithValue("TipoKms", mintTipoKms)
        Command.Parameters.AddWithValue("Cod_Vehiculo", mstrCod_Vehiculo)
        Command.Parameters.AddWithValue("Numero_Esclavo", mstrNumero_Esclavo)
        Command.Parameters.AddWithValue("cIdClien01", mintcIdClien01)
        Command.Parameters.AddWithValue("cCodigoC01", mstrcCodigoC01)
        Command.Parameters.AddWithValue("cRazonSo01", mstrcRazonSo01)
        Command.Parameters.AddWithValue("Placas", mstrPlacas)
        Command.Parameters.AddWithValue("Marca", mstrMarca)
        Command.Parameters.AddWithValue("Modelo", mstrModelo)
        Command.Parameters.AddWithValue("Serie", mstrSerie)
        Command.Parameters.AddWithValue("Motor", mstrMotor)
        Command.Parameters.AddWithValue("Estatus", mintEstatus)
        Command.Parameters.AddWithValue("Importe01", mdblImporte01)
        Command.Parameters.AddWithValue("Importe02", mdblImporte02)
        Command.Parameters.AddWithValue("Importe03", mdblImporte03)
        Command.Parameters.AddWithValue("Importe04", mdblImporte04)
        Command.Parameters.AddWithValue("Importe05", mdblImporte05)
        Command.Parameters.AddWithValue("Texto01", mstrTexto01)
        Command.Parameters.AddWithValue("Texto02", mstrTexto02)
        Command.Parameters.AddWithValue("Texto03", mstrTexto03)
        Command.Parameters.AddWithValue("Texto04", mstrTexto04)
        Command.Parameters.AddWithValue("Texto05", mstrTexto05)

        cnConn.Open()
        Dim RowsAffected As Integer
        Try
            RowsAffected = Command.ExecuteNonQuery
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Aviso")
        End Try

        If RowsAffected > 0 Then
            cnConn.Close()
        Else
            MsgBox("No fue posible insertar el autobus " & mstrNoEconomico, MsgBoxStyle.Exclamation, "Aviso")
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End If
    End Sub
    Public Sub Actualizar()
        Dim strSqlQry As String
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        strSqlQry = "Update Autobuses Set " & _
                    " NoEconomico=@NoEconomico, " & _
                    " IdRuta=@IdRuta, " & _
                    " TipoKms=@TipoKms, " & _
                    " Cod_Vehiculo=@Cod_Vehiculo, " & _
                    " Numero_Esclavo=@Numero_Esclavo, " & _
                    " cRazonSo01=@cRazonSo01, " & _
                    " Placas=@Placas, " & _
                    " Marca=@Marca, " & _
                    " Modelo=@Modelo, " & _
                    " Serie=@Serie, " & _
                    " Motor=@Motor, " & _
                    " Estatus=@Estatus, " & _
                    " Importe01=@Importe01, " & _
                    " Importe02=@Importe02, " & _
                    " Importe03=@Importe03, " & _
                    " Importe04=@Importe04, " & _
                    " Importe05=@Importe05, " & _
                    " Texto01=@Texto01, " & _
                    " Texto02=@Texto02, " & _
                    " Texto03=@Texto03, " & _
                    " Texto04=@Texto04," & _
                    " Texto05=@Texto05 " & _
                    " where IdAutobus=@IdAutobus"

        Dim Command As New SqlCommand(strSqlQry, cnConn)
        Command.Parameters.AddWithValue("IdAutobus", mintIdAutobus)
        Command.Parameters.AddWithValue("NoEconomico", mstrNoEconomico)
        Command.Parameters.AddWithValue("IdRuta", mintIdRuta)
        Command.Parameters.AddWithValue("TipoKms", mintTipoKms)
        Command.Parameters.AddWithValue("Cod_Vehiculo", mstrCod_Vehiculo)
        Command.Parameters.AddWithValue("Numero_Esclavo", mstrNumero_Esclavo)
        Command.Parameters.AddWithValue("cIdClien01", mintcIdClien01)
        Command.Parameters.AddWithValue("cCodigoC01", mstrcCodigoC01)
        Command.Parameters.AddWithValue("cRazonSo01", mstrcRazonSo01)
        Command.Parameters.AddWithValue("Placas", mstrPlacas)
        Command.Parameters.AddWithValue("Marca", mstrMarca)
        Command.Parameters.AddWithValue("Modelo", mstrModelo)
        Command.Parameters.AddWithValue("Serie", mstrSerie)
        Command.Parameters.AddWithValue("Motor", mstrMotor)
        Command.Parameters.AddWithValue("Estatus", mintEstatus)
        Command.Parameters.AddWithValue("Importe01", mdblImporte01)
        Command.Parameters.AddWithValue("Importe02", mdblImporte02)
        Command.Parameters.AddWithValue("Importe03", mdblImporte03)
        Command.Parameters.AddWithValue("Importe04", mdblImporte04)
        Command.Parameters.AddWithValue("Importe05", mdblImporte05)
        Command.Parameters.AddWithValue("Texto01", mstrTexto01)
        Command.Parameters.AddWithValue("Texto02", mstrTexto02)
        Command.Parameters.AddWithValue("Texto03", mstrTexto03)
        Command.Parameters.AddWithValue("Texto04", mstrTexto04)
        Command.Parameters.AddWithValue("Texto05", mstrTexto05)

        cnConn.Open()

        Dim RowsAffected As Integer = Command.ExecuteNonQuery

        If RowsAffected > 0 Then
            cnConn.Close()
        Else
            MsgBox("No fue posible actualizar el autobus " & mstrNoEconomico, MsgBoxStyle.Exclamation, "Aviso")
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End If
    End Sub
    Public Sub ActualizarNuevo()
        Dim strSqlQry As String
        Dim cnConn As New SqlConnection
        cnConn.ConnectionString = CitraConnection

        strSqlQry = "Update Autobuses Set " & _
                    " NoEconomico=SUBSTRING(@NoEconomico,1,40), " & _
                    " IdRuta=@IdRuta, " & _
                    " cRazonSo01=SUBSTRING(@cRazonSo01,1,20), " & _
                    " Placas=SUBSTRING(@Placas,1,100), " & _
                    " Marca=SUBSTRING(@Marca,1,100), " & _
                    " Modelo=SUBSTRING(@Modelo,1,50), " & _
                    " Serie=SUBSTRING(@Serie,1,100), " & _
                    " Motor=SUBSTRING(@Motor,1,100), " & _
                    " Estatus=@Estatus, " & _
                    " Importe05=@Importe05 " & _
                    " where IdAutobus=@IdAutobus"



        ' 


        Dim Command As New SqlCommand(strSqlQry, cnConn)
        Command.Parameters.AddWithValue("IdAutobus", mintIdAutobus)
        Command.Parameters.AddWithValue("NoEconomico", mstrNoEconomico)
        Command.Parameters.AddWithValue("IdRuta", mintIdRuta)
        Command.Parameters.AddWithValue("cCodigoC01", mstrcCodigoC01)
        Command.Parameters.AddWithValue("cRazonSo01", mstrcRazonSo01)
        Command.Parameters.AddWithValue("Placas", mstrPlacas)
        Command.Parameters.AddWithValue("Marca", mstrMarca)
        Command.Parameters.AddWithValue("Modelo", mstrModelo)
        Command.Parameters.AddWithValue("Serie", mstrSerie)
        Command.Parameters.AddWithValue("Motor", mstrMotor)
        Command.Parameters.AddWithValue("Estatus", mintEstatus)
        Command.Parameters.AddWithValue("Importe01", mdblImporte01)
        Command.Parameters.AddWithValue("Importe02", mdblImporte02)
        Command.Parameters.AddWithValue("Importe03", mdblImporte03)
        Command.Parameters.AddWithValue("Importe04", mdblImporte04)
        Command.Parameters.AddWithValue("Importe05", mdblImporte05)
        Command.Parameters.AddWithValue("Texto01", mstrTexto01)
        Command.Parameters.AddWithValue("Texto02", mstrTexto02)
        Command.Parameters.AddWithValue("Texto03", mstrTexto03)
        Command.Parameters.AddWithValue("Texto04", mstrTexto04)
        Command.Parameters.AddWithValue("Texto05", mstrTexto05)

        cnConn.Open()

        Dim RowsAffected As Integer = Command.ExecuteNonQuery

        If RowsAffected > 0 Then
            cnConn.Close()
            'MsgBox("Bien")

        Else
            MsgBox("No fue posible actualizar el autobus " & mstrNoEconomico, MsgBoxStyle.Exclamation, "Aviso")
            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If
        End If
    End Sub
    Public Sub ActualizarEnAdminPAQ()
        Dim strSqlQry As String
        Dim cnConn As New OdbcConnection
        cnConn.ConnectionString = AdminPAQConnection1

        strSqlQry = "Update MGW10002 Set " & _
                    " cRazonSo01='" & mstrcRazonSo01 & "'," & _
                    " cIdValor01=" & mintIdRuta & "," & _
                    " cEstatus=" & mintEstatus & "," & _
                    " cTextoEx01='" & mstrPlacas & "', " & _
                    " cTextoEx02='" & mstrMarca & "', " & _
                    " cTextoEx03='" & mstrModelo & "', " & _
                    " cTextoEx04='" & mstrSerie & "', " & _
                    " cTextoEx05='" & mstrMotor & "' " & _
                    " where cIdClien01=" & mintcIdClien01

        Dim cmdInsert As New OdbcCommand
        cmdInsert.Connection = cnConn
        cmdInsert.CommandText = strSqlQry


        Try

            cnConn.Open()
            cmdInsert.ExecuteNonQuery()
            cnConn.Close()

        Catch ex As OdbcException
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If

        End Try

    End Sub
    Public Sub ActualizarAdminPAQ()
        Dim strSqlQry As String
        Dim cnConn As New OdbcConnection
        cnConn.ConnectionString = AdminPAQConnection1

        strSqlQry = "Update MGW10002 Set " & _
                    " cRazonSo01='" & mstrcRazonSo01 & "'," & _
                    " cIdValor01=" & mintIdRuta & "," & _
                    " cEstatus=" & mintEstatus & "," & _
                    " cTextoEx01='" & mstrPlacas & "', " & _
                    " cTextoEx02='" & mstrMarca & "', " & _
                    " cTextoEx03='" & mstrModelo & "', " & _
                    " cTextoEx04='" & mstrSerie & "', " & _
                    " cTextoEx05='" & mstrMotor & "' " & _
                    " where cIdClien01=" & mintcIdClien01

        Dim cmdInsert As New OdbcCommand
        cmdInsert.Connection = cnConn
        cmdInsert.CommandText = strSqlQry


        Try

            cnConn.Open()
            cmdInsert.ExecuteNonQuery()
            cnConn.Close()

        Catch ex As OdbcException
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Catch ex As Exception
            MessageBox.Show(ex.Message, "System Message", MessageBoxButtons.OK, MessageBoxIcon.Error)

        Finally

            If cnConn.State = ConnectionState.Open Then
                cnConn.Close()
            End If

        End Try

    End Sub
End Class
